﻿using Common;
using DAL.DBUtility;
using Enums;
using Enums.Sys;
using Model.Sys;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL.Sys
{
   public class SysUserDAL
    {
        /// <summary>
        /// 获取单个用户信息 根据登录名查
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public SysUserModel GetModel(string loginName)
        {
            SysUserModel model = new SysUserModel();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  select u.*,e.EnterpriseName,r.RoleName from Sys_User u with(nolock) left join Organization_Enterprise e on u.EnterpriseId=e.ID left join sys_role r on r.ID=u.RoleId where u.LoginName = @LoginName and e.ViewDel='" + EnumManagement.GetEnumValue(SysEnum.NoDele) + "' and e.ViewFlag='" + EnumManagement.GetEnumValue(SysEnum.Enable) + "'");
            SqlParameter[] parameters = {
					new SqlParameter("@LoginName", loginName)
			};
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(strSql.ToString(), parameters);
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }
        #region 公共Model
        /// <summary>
        /// 公共Model
        /// </summary>
        /// <param name="result"></param>
        /// <returns></returns>
        private SysUserModel CommonModel(SqlDataReader sdr)
        {
            SysUserModel model = new SysUserModel();
            model.ID = PageValidate.ConvertToTrimInt(sdr["ID"]);
            model.ViewDel = PageValidate.ConvertToTrimInt(sdr["ViewDel"]);
            model.ViewFlag = PageValidate.ConvertToTrimInt(sdr["ViewFlag"]);

            model.EnterpriseId = PageValidate.ConvertToTrimInt(sdr["EnterpriseId"]);
            model.RealName = PageValidate.ConvertToTrimString(sdr["RealName"]);
            model.LoginName = PageValidate.ConvertToTrimString(sdr["LoginName"]);
            model.Pwd = PageValidate.ConvertToTrimString(sdr["Pwd"]);
            model.Modile = PageValidate.ConvertToTrimString(sdr["Modile"]);
            model.Remark = PageValidate.ConvertToTrimString(sdr["Remark"]);
            model.RoleId = PageValidate.ConvertToTrimInt(sdr["RoleId"]);
            model.DisableTime = PageValidate.ConvertToDateTime(sdr["DisableTime"]);

            model.Sequence = PageValidate.ConvertToTrimInt(sdr["Sequence"]);
            model.CreateLoginID = PageValidate.ConvertToTrimInt(sdr["CreateLoginID"]);
            model.CreateLoginName = PageValidate.ConvertToTrimString(sdr["CreateLoginName"]);
            model.CreateDate = PageValidate.ConvertToDateTime(sdr["CreateDate"]);
            model.ChangeLoginID = PageValidate.ConvertToTrimInt(sdr["ChangeLoginID"]);
            model.ChangeLoginName = PageValidate.ConvertToTrimString(sdr["ChangeLoginName"]);
            model.ChangeDate = PageValidate.ConvertToDateTime(sdr["ChangeDate"]);
            //公司名称
            model.EnterpriseName = PageValidate.ConvertToTrimString(sdr["EnterpriseName"]);
            model.IsManage = PageValidate.ConvertToTrimInt(sdr["IsManage"]);
            model.RoleName = PageValidate.ConvertToTrimString(sdr["RoleName"]);

            return model;
        }
        #endregion

        public List<SysUserModel> GetList(string strWhere, string term)
        {
            //  string sql = @"  select * from Sys_User where ViewDel=@ViewDel and EnterpriseId=@EnterpriseId";
            string sql = @"select *,oe.EnterpriseName,sr.RoleName  from Sys_User  su 
                   left Join Organization_Enterprise oe on su.EnterpriseId=oe.ID
                   left join Sys_Role sr on su.RoleId=sr.ID where su.ViewDel=@ViewDel {0}   and  (su.LoginName like '%" + @term + "%' or  oe.EnterpriseName like '%" + @term + "%')";
            sql = string.Format(sql, strWhere);
            SqlParameter[] parameters = {
                                            new SqlParameter("@ViewDel", EnumManagement.GetEnumValue(SysEnum.NoDele)),
                                             new SqlParameter("@term",term)
			};
            return ListDataSet.DataSetToIList<SysUserModel>(DbHelperSQL.Query(sql, parameters)).ToList();
        }


       /// <summary>
       /// 新增
       /// </summary>
       /// <param name="model"></param>
       /// <returns></returns>
        public ResultEntity AddModel(SysUserModel model)
        {
            
            StringBuilder strSql = new StringBuilder();
          
            strSql.Append(@"INSERT INTO [dbo].[Sys_User]
           ([ViewDel]
           ,[ViewFlag]
           ,[EnterpriseId]
           ,[RealName]
           ,[LoginName]
           ,[Pwd]
           ,[Modile]
           ,[Remark]
           ,[RoleId]
           ,[DisableTime]
           ,[Sequence]
           ,[CreateLoginID]
           ,[CreateLoginName]
           ,[CreateDate]
           ,[ChangeLoginID]
           ,[ChangeLoginName]
           ,[ChangeDate],[Province]
           ,[City]) VALUES(");
            strSql.Append(@"@ViewDel
           ,@ViewFlag
           ,@EnterpriseId
           ,@RealName
           ,@LoginName
           ,@Pwd
           ,@Modile
           ,@Remark
           ,@RoleId
           ,@DisableTime
           ,@Sequence
           ,@CreateLoginID
           ,@CreateLoginName
           ,@CreateDate
           ,@ChangeLoginID
           ,@ChangeLoginName
           ,@ChangeDate,@Province
           ,@City ) ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                new SqlParameter("@ViewDel",PageValidate.ConvertToTrimInt(model.ViewDel)),
                new SqlParameter("@ViewFlag",PageValidate.ConvertToTrimInt(model.ViewFlag)),
                new SqlParameter("@EnterpriseId",PageValidate.ConvertToTrimInt(model.EnterpriseId)),
                new SqlParameter("@RealName",PageValidate.ConvertToTrimString(model.RealName)),
                new SqlParameter("@LoginName",PageValidate.ConvertToTrimString(model.LoginName)),
                new SqlParameter("@Pwd",PageValidate.ConvertToTrimString(EDS3.DESEnCode(model.Pwd))),
                new SqlParameter("@Modile",PageValidate.ConvertToTrimString(model.Modile)),
                new SqlParameter("@Remark",PageValidate.ConvertToTrimString(model.Remark)),
                new SqlParameter("@RoleId",PageValidate.ConvertToTrimInt(model.RoleId)),
      
                new SqlParameter("@DisableTime",PageValidate.ConvertToDateTime(model.DisableTime)),
                new SqlParameter("@Sequence",PageValidate.ConvertToTrimInt(model.Sequence)),
                new SqlParameter("@CreateLoginID",PageValidate.ConvertToTrimInt(model.CreateLoginID)),
                new SqlParameter("@CreateLoginName",PageValidate.ConvertToTrimString(model.CreateLoginName)),
                new SqlParameter("@CreateDate",PageValidate.ConvertToDateTime(model.CreateDate)),
                new SqlParameter("@ChangeLoginID",PageValidate.ConvertToTrimInt(model.ChangeLoginID)),
                new SqlParameter("@ChangeLoginName",PageValidate.ConvertToTrimString(model.ChangeLoginName)),
                new SqlParameter("@ChangeDate",PageValidate.ConvertToDateTime(model.ChangeDate)),
                new SqlParameter("@Province",PageValidate.ConvertToTrimInt(model.Province)),
                new SqlParameter("@City",PageValidate.ConvertToTrimInt(model.City))
        };
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(strSql.ToString(), parameters));
        }

        #region 修改
        public ResultEntity Update(SysUserModel model)
        {
            StringBuilder strSql = new StringBuilder();
        
            strSql.Append(@"UPDATE [dbo].[Sys_User]
                                SET  [EnterpriseId] =@EnterpriseId				
                                    ,[RealName] =@RealName					
                                    ,[LoginName] =@LoginName
                                    ,Pwd =@Pwd
                                    ,[Modile] =@Modile					
                                    ,[Remark] =@Remark					
                                    ,[RoleId] =@RoleId					
                                    ,[Sequence] =@Sequence					
                                    ,[ChangeLoginID] =@ChangeLoginID			
                                    ,[ChangeLoginName] =@ChangeLoginName			
                                    ,[ChangeDate] =@ChangeDate
                                    ,[Province] =@Province			
                                    ,[City] =@City			
                                where ID=@ID");

            SqlParameter[] parameters = {
                           
                            new SqlParameter("@EnterpriseId",PageValidate.ConvertToTrimInt(model.EnterpriseId)),
                            new SqlParameter("@RealName",PageValidate.ConvertToTrimString(model.RealName)),
                            new SqlParameter("@LoginName",PageValidate.ConvertToTrimString(model.LoginName)),
                            new SqlParameter("@Pwd",PageValidate.ConvertToTrimString(EDS3.DESEnCode(model.Pwd))),
                            new SqlParameter("@Modile",PageValidate.ConvertToTrimString(model.Modile)),
                            new SqlParameter("@Remark",PageValidate.ConvertToTrimString(model.Remark)),
                            new SqlParameter("@RoleId",PageValidate.ConvertToTrimInt(model.RoleId)),
                         
                            new SqlParameter("@Sequence",PageValidate.ConvertToTrimInt(model.Sequence)),
                            new SqlParameter("@ChangeLoginID",PageValidate.ConvertToTrimInt(model.ChangeLoginID)),
                            new SqlParameter("@ChangeLoginName",PageValidate.ConvertToTrimString(model.ChangeLoginName)),
                            new SqlParameter("@ChangeDate",PageValidate.ConvertToDateTime(model.ChangeDate)),
                            new SqlParameter("@Province",PageValidate.ConvertToTrimInt(model.Province)),
                            new SqlParameter("@City",PageValidate.ConvertToTrimInt(model.City)),
                            new SqlParameter("@ID",PageValidate.ConvertToTrimInt(model.ID))
                                        };
            return DbHelperSQL.CommonResult(DbHelperSQL.ExecuteSql(strSql.ToString(), parameters));

        }
        #endregion

        #region 获取单个用户信息 根据ID查
        public SysUserModel GetModelById(string id)
        {


            string sql = @"SELECT *,e.EnterpriseName,r.RoleName
                  from Sys_User u,Organization_Enterprise e,
                  Sys_Role r 
                  where u.EnterpriseId=e.ID and r.ID=u.RoleId and u.ID=@ID ";
            SqlParameter[] parameters = {
					new SqlParameter("@ID",id)
			};
            SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parameters);
            SysUserModel model = new SysUserModel();
            if (sdr.Read())
            {
                model = CommonModel(sdr);
            }
            sdr.Close();
            return model;
        }

        #endregion
    }
}
